<?php
class Author {

    public function __construct() {
        $this->SQL = MySQL::getInstance();
    }

    /*
     * getAll - get all authors for a given location
     * @param locationId int location id
     * @return array nested array of form array(array(id, name))
     */
    public function getAll($locationId) {
        return $this->SQL->fquery('
            SELECT id, name
            FROM authors
            WHERE location_id = %d
            ORDER BY name',
            $locationId
        );
    }

    /*
     * get - get author info by id (not user id)
     * @param id int author or
     * @return array of form array(name, location_id, user_id)
     */
    public function get($id) {
        return $this->SQL->fetch_single('
            SELECT name, location_id, user_id
            FROM authors
            WHERE id = %d
            LIMIT 1',
            $id
        );
    }

    /*
     * getByRoute - get all authors registered to a location, based on a route in that location
     * @param routeId int route id
     * @return array list of authors of form array(id, name)
     */
    public function getByRoute($routeId) {
        return $this->SQL->fquery('
            SELECT a.id, a.name
            FROM routes r
            JOIN authors a
                ON a.location_id = r.location_id
            WHERE r.id = %d',
            $routeId
        );
    }

    /*
     * getStats - get author statistics by author id (not user id)
     * @param id int author id
     * @return array form array(count, min_diff, max_diff, avg_diff, first_created)
     */
    public function getStats($id) {
        $ret = $this->SQL->fetch_single('
            SELECT COUNT(r.id) AS count, MIN(d.value) AS min_diff, MAX(d.value) AS max_diff, AVG(d.value) AS avg_diff, MIN(r.created) AS first_created
            FROM routes r
            JOIN difficulties d
                ON d.id = r.difficulty_id
            WHERE r.author_id = %d
            LIMIT 1',
            $id
        );
        return array(
            'count'         => $ret['count'],
            'min_diff'      => $ret['min_diff'],
            'max_diff'      => $ret['max_diff'],
            'avg_diff'      => (int)$ret['avg_diff'],
            'first_created' => $ret['first_created'],
        );
    }

    /*
     * getLatestRoutes - get x newest routes created by this author
     * @param id int author id
     * @param count int number of max routes to return
     * @return array nested array of form array(id, colour_id, difficulty_id, created)
     */
    public function getLatestRoutes($id, $count) {
        return $this->SQL->fquery('
            SELECT r.id, r.colour_id, r.difficulty_id, r.created, r.name, rt.unicode, rt.type AS route_type
            FROM routes r
            JOIN route_types rt
                ON rt.id = r.type_id
            WHERE r.author_id = %d
            ORDER BY r.created DESC
            LIMIT %d',
            $id,
            $count
        );
    }
}
?>
